﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using FX.CC.Andon.Entity.BasicDataManage;
using FX.CC.Andon.Utility.Common;
using FX.CC.Andon.Utility.DataBase;

namespace FX.CC.Andon.Service.BasicDataManage
{
    /// <summary>
    /// 设备数据层
    /// </summary>
    public class EquipmentService : DbHelperBase
    {
        /// <summary>
        /// 获取设备下拉列表
        /// </summary>
        /// <returns></returns>
        public List<EquipmentEntity> GetEquipmentListByCombobox(EquipmentQcEntity qcmodel)
        {
            var list = new List<EquipmentEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT EqID,EqCode,EqName FROM UT_Equipment where RecStatus=1 ");
            if (!string.IsNullOrEmpty(qcmodel.LineID))
            {
                strSql.Append(" and LineID='" + qcmodel.LineID + "'");
            }
            strSql.Append(" order by InputTime,EqCode ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new EquipmentEntity
                            {
                                EqCode =
                                    reader["EqCode"] == obj
                                        ? null
                                        : Convert.ToString(reader["EqCode"]),
                                EqName =
                                    reader["EqName"] == obj
                                        ? null
                                        : Convert.ToString(reader["EqName"])
                            };
                            if (reader["EqID"] != DBNull.Value)
                                model.EqID = new Guid(reader["EqID"].ToString());
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
        /// <summary>
        /// 添加设备
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool AddEquipment(EquipmentEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [UT_Equipment]([EqID],[LineID],[EqCode],[EqName],[EqDesc],[RecStatus]) ");
            strSql.Append(" VALUES(@EqID,@LineID,@EqCode,@EqName,@EqDesc,@RecStatus) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@EqID", model.EqID);
                    Db.AddInParameter(cmd, "@LineID", model.LineID);
                    Db.AddInParameter(cmd, "@EqCode", model.EqCode);
                    Db.AddInParameter(cmd, "@EqName", model.EqName);
                    Db.AddInParameter(cmd, "@EqDesc", model.EqDesc);
                    Db.AddInParameter(cmd, "@RecStatus", model.RecStatus);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
        /// <summary>
        /// 修改设备
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool ModifyEquipment(EquipmentEntity model)
        {
            bool result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [UT_Equipment] SET [LineID] = @lnID,[EqCode] = @EqCode,[EqName] = @EqName,[EqDesc] = @EqDesc WHERE [EqID] = @EqID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@lnID", model.LineID);
                    Db.AddInParameter(cmd, "@EqCode", model.EqCode);
                    Db.AddInParameter(cmd, "@EqName", model.EqName);
                    Db.AddInParameter(cmd, "@EqDesc", model.EqDesc);
                    Db.AddInParameter(cmd, "@EqID", model.EqID);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
        /// <summary>
        /// 获取设备单条信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public EquipmentEntity GetEquipmentInfo(string id)
        {
            var model = new EquipmentEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [EqID],[LineID],[EqCode],[EqName],[EqDesc],[RecStatus],[InputTime] FROM [UT_Equipment] where [EqID]=@EqID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                Db.AddInParameter(cmd, "@EqID", id);
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["EqID"] != DBNull.Value)
                                model.EqID = new Guid(reader["EqID"].ToString());
                            if (reader["LineID"] != DBNull.Value)
                                model.LineID = new Guid(reader["LineID"].ToString());
                            model.EqCode = reader["EqCode"] == obj ? null : Convert.ToString(reader["EqCode"]);
                            model.EqName = reader["EqName"] == obj ? null : Convert.ToString(reader["EqName"]);
                            model.EqDesc = reader["EqDesc"] == obj ? null : Convert.ToString(reader["EqDesc"]);
                            if (reader["RecStatus"] != DBNull.Value)
                                model.RecStatus = Convert.ToBoolean(reader["RecStatus"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }


        /// <summary>
        /// 获取总记录数
        /// </summary>
        /// <param name="qcmodel"></param>
        /// <returns></returns>
        public int GetEquipmentCount(EquipmentQcEntity qcmodel)
        {
            var count = 0;
            var strSql = new StringBuilder();
            strSql.Append(" select  count(*) AS [count] FROM UT_Equipment AS A WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.EqCode))
            {
                strSql.Append(" and EqCode like '%" + qcmodel.EqCode + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.EqName))
            {
                strSql.Append(" and EqName like '%" + qcmodel.EqName + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.LineID))
            {
                strSql.Append(" and LineID ='" + qcmodel.LineID + "'");
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        /// <summary>
        /// 获取设备列表分页
        /// </summary>
        /// <param name="qcmodel"></param>
        /// <returns></returns>
        public List<EquipmentEntity> GetEquipmentList(EquipmentQcEntity qcmodel)
        {
            var list = new List<EquipmentEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            int start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            int end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by InputTime) AS idx,[EqID],[LineID],[EqCode],[EqName],[EqDesc],[RecStatus],[InputTime]  FROM UT_Equipment where 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.EqCode))
            {
                strSql.Append(" and EqCode like '%" + qcmodel.EqCode + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.EqName))
            {
                strSql.Append(" and EqName like '%" + qcmodel.EqName + "%' ");
            }
            if (!string.IsNullOrEmpty(qcmodel.LineID))
            {
                strSql.Append(" and LineID ='" + qcmodel.LineID + "'");
            }
            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new EquipmentEntity();
                            if (reader["EqID"] != DBNull.Value)
                                model.EqID = new Guid(reader["EqID"].ToString());
                            if (reader["LineID"] != DBNull.Value)
                                model.LineID = new Guid(reader["LineID"].ToString());
                            model.EqCode = reader["EqCode"] == obj ? null : Convert.ToString(reader["EqCode"]);
                            model.EqName = reader["EqName"] == obj ? null : Convert.ToString(reader["EqName"]);
                            model.EqDesc = reader["EqDesc"] == obj ? null : Convert.ToString(reader["EqDesc"]);
                            if (reader["RecStatus"] != DBNull.Value)
                                model.RecStatus = Convert.ToBoolean(reader["RecStatus"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public List<EquipmentEntity> GetEquipmentListById(string id)
        {
            var list = new List<EquipmentEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT [EqID],[LineID],[EqCode],[EqName],[EqDesc],[RecStatus] FROM [UT_Equipment] WHERE LineID=@lnID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@lnID", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new EquipmentEntity();
                            if (reader["EqID"] != DBNull.Value)
                                model.EqID = new Guid(reader["EqID"].ToString());
                            if (reader["LineID"] != DBNull.Value)
                                model.LineID = new Guid(reader["LineID"].ToString());
                            model.EqCode = reader["EqCode"] == obj ? null : Convert.ToString(reader["EqCode"]);
                            model.EqName = reader["EqName"] == obj ? null : Convert.ToString(reader["EqName"]);
                            model.EqDesc = reader["EqDesc"] == obj ? null : Convert.ToString(reader["EqDesc"]);
                            if (reader["RecStatus"] != DBNull.Value)
                                model.RecStatus = Convert.ToBoolean(reader["RecStatus"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public void ImportEq(List<EquipmentEntity> list)
        {
            using (var conn = Db.GetConnection() as SqlConnection)
            {
                if (conn == null)
                {
                    throw new InvalidOperationException("No Valid Database Connection!");
                }
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                using (var bcp = new SqlBulkCopy(conn))
                {
                    var mapEqId =
                        new SqlBulkCopyColumnMapping("EqID", "EqID");
                    bcp.ColumnMappings.Add(mapEqId);

                    var mapLineID =
                        new SqlBulkCopyColumnMapping("LineID", "LineID");
                    bcp.ColumnMappings.Add(mapLineID);

                    var mapEqCode =
                        new SqlBulkCopyColumnMapping("EqCode", "EqCode");
                    bcp.ColumnMappings.Add(mapEqCode);

                    var mapEqName =
                        new SqlBulkCopyColumnMapping("EqName", "EqName");
                    bcp.ColumnMappings.Add(mapEqName);

                    var mapEqDesc =
                        new SqlBulkCopyColumnMapping("EqDesc", "EqDesc");
                    bcp.ColumnMappings.Add(mapEqDesc);

                    var mapRecStatus =
                        new SqlBulkCopyColumnMapping("RecStatus", "RecStatus");
                    bcp.ColumnMappings.Add(mapRecStatus);

                    bcp.DestinationTableName = "UT_Equipment";

                    try
                    {
                        bcp.WriteToServer(Collection.ToDataTable(list));
                    }
                    catch (Exception ex)
                    {
                        EventsLog.WriteLog("ErrorInfo" + ex.Message);
                    }
                    finally
                    {
                        if (conn.State != ConnectionState.Closed)
                            conn.Close();
                    }
                }
            }
        }
        public void UpdateEq(List<EquipmentEntity> list)
        {
            using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT EqID,LineID,EqCode,EqName,EqDesc,RecStatus INTO [#EquipmentTmp] FROM UT_Equipment Where 1=2";
                    cmd.ExecuteNonQuery();
                }
                using (var bcp = new SqlBulkCopy(conn))
                {
                    var mapEqId =
                        new SqlBulkCopyColumnMapping("EqID", "EqID");
                    bcp.ColumnMappings.Add(mapEqId);

                    var mapLineID =
                        new SqlBulkCopyColumnMapping("LineID", "LineID");
                    bcp.ColumnMappings.Add(mapLineID);

                    var mapEqCode =
                        new SqlBulkCopyColumnMapping("EqCode", "EqCode");
                    bcp.ColumnMappings.Add(mapEqCode);

                    var mapEqName =
                        new SqlBulkCopyColumnMapping("EqName", "EqName");
                    bcp.ColumnMappings.Add(mapEqName);


                    var mapEqDesc =
                        new SqlBulkCopyColumnMapping("EqDesc", "EqDesc");
                    bcp.ColumnMappings.Add(mapEqDesc);

                    var mapRecStatus =
                        new SqlBulkCopyColumnMapping("RecStatus", "RecStatus");
                    bcp.ColumnMappings.Add(mapRecStatus);

                    bcp.DestinationTableName = "#EquipmentTmp";

                    try
                    {
                        bcp.WriteToServer(Collection.ToDataTable(list));
                    }
                    catch
                    {
                        throw;
                    }
                }

                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText =
@"Update A Set A.EqCode=B.EqCode, A.EqName=B.EqName,A.EqDesc=B.EqDesc
From #EquipmentTmp B Inner Join UT_Equipment A ON A.EqID=B.EqID;
Drop Table #EquipmentTmp;";
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// CT值导入Excel时用到了，验证设备编号
        /// </summary>
        /// <returns></returns>
        public List<EquipmentEntity> GetEquipment()
        {
            var list = new List<EquipmentEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT [EqID],[EqCode] FROM UT_Equipment ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new EquipmentEntity();
                            if (reader["EqID"] != DBNull.Value)
                                model.EqID = new Guid(reader["EqID"].ToString());
                            model.EqCode = reader["EqCode"] == obj ? null : Convert.ToString(reader["EqCode"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public string GetCheckEqCode(string id)
        {
            object obj = DBNull.Value;
            var eqId = "";
            var strSql = new StringBuilder();
            strSql.Append(" select  EqID FROM UT_Equipment  WHERE EqCode=@EqCode ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@EqCode", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            eqId = reader["EqID"] == obj ? null : Convert.ToString(reader["EqID"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return eqId;
            }
        }

        public Guid GetEqId(string eqCode)
        {
            var eqId = new Guid();
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [EqID] FROM [UT_Equipment] Where EqCode=@EqCode ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {

                try
                {
                    Db.AddInParameter(cmd, "@EqCode", eqCode);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["EqID"] != DBNull.Value)
                                eqId = new Guid(reader["EqID"].ToString());
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return eqId;
            }
        }

        public EquipmentEntity GetByCode(string eqCode)
        {
            var obj = new EquipmentEntity();
            var strSql = new StringBuilder();
            strSql.Append(" SELECT EqID, EqName FROM [UT_Equipment] Where EqCode=@EqCode ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {

                try
                {
                    Db.AddInParameter(cmd, "@EqCode", eqCode);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            obj.EqID = ReaderHelper.GetValue<Guid>(reader, "EqID");
                            obj.EqCode = eqCode;
                            obj.EqName = ReaderHelper.GetValue<string>(reader, "EqName");
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return obj;
            }
        }
    }
}
